package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;
import com.cn.wanxi.util.MD5Utils;
import jdk.nashorn.internal.scripts.JD;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.SimpleFormatter;

/**
 * 数据实现层
 * 1.封装sql语句
 * 2.增加修改删除   调用jdbc的方法并返回影响的条数
 * 3.查询   调用jdbc的查询方法，然后封装需要的数据，并返回数据
 */
public class UserImpl implements UserDao {
    /**
     * dao层数据应该从数据库里面来
     * 链接数据库的方式JDBC
     *
     * @param userModel
     * @return
     */
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
//        如果有值，则返回对象，如果没有则返回null
        String sql = "select * from  user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        UserModel model = null;
        ResultSet resultSet = JDBC.query(sql);

        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
//            JDBC.close();
        }
//        final和finally和finalize的区别？
        return model;
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user order by id desc";
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public UserModel findByUsername(String username) {
        String sql = "select * from user where username ='" + username + "'";
        UserModel model = null;
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    //    public static UserModel aa() {
//        System.out.println("aa");
//        return null;
//    }
//
//    public static UserModel bb() {
//        System.out.println("bb");
//        return new UserModel();
//    }
//
//    public static void main(String[] args) {
//        UserModel userModel = null;
//        try {
//            userModel = aa();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            userModel = bb();
//        }
//        System.out.println(userModel);
//    }
    public static void main(String[] args) {
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String format = simpleDateFormat.format(date);
        System.out.println(format);
    }

    @Override
    public int add(UserModel userModel) {
        /**
         * Java 拿到系統的當前時間
         *
         */
        String sql = "INSERT INTO `user` (" +
                "`address`, `create_time`, `email`, `enable`," +
                " `password`, `phone`, `sex`, " +
                "`update_time`, `username`, " +
                "`hobby`, `birthday`) VALUES (" +
                "'" + userModel.getAddress() + "', now(), '" + userModel.getEmail() + "', '" + userModel.getEnable() + "', " +
                "'" + MD5Utils.stringToMD5(userModel.getPassword()) + "', '" + userModel.getPhone() + "', '" + userModel.getSex() + "'," +
                " now(), '" + userModel.getUsername() + "', " +
                "'" + userModel.getHobby() + "', '" + userModel.getBirthday() + "');";
        return JDBC.update(sql);
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "";
        return JDBC.update(sql);
    }

    @Override
    public int delete(UserModel userModel) {
//        String sql = "select  * from user where id=? username=? and password=? and sex=?";
//        List<String> list = new ArrayList<>();
//        list.add(userModel.getId() + "");
//        list.add(userModel.getUsername());
//        list.add(userModel.getPassword());
//        list.add(userModel.getSex());
        String sql = "delete from user where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        StringBuilder sql = new StringBuilder("select * from user where 1=1 ");
        if (!"".equals(userModel.getUsername())) {
            sql.append(" and username like '%").append(userModel.getUsername()).append("%'  ");
        }
        if (!"".equals(userModel.getPhone())) {
            sql.append(" and phone like '%").append(userModel.getPhone()).append("%' ");
        }
        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
            sql.append(" and  birthday >= '").append(userModel.getStart()).append("' ");
        }
        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql.append(" and  birthday <= '").append(userModel.getEnd()).append("' ");
        }
        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql.append(" and  birthday between '").append(userModel.getStart()).append("' and '").append(userModel.getEnd()).append("' ");
        }
        if (!"".equals(userModel.getSex())) {
            sql.append(" and sex = '").append(userModel.getSex()).append("' ");
        }
        if (userModel.getEnable() != -1) {
            sql.append(" and enable ='").append(userModel.getEnable()).append("' ");
        }
        if (!"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            sql.append(" and (");
            for (int i = 0; i < hobbys.length; i++
            ) {
                if (i == 0) {
                    sql.append("  find_in_set('").append(hobbys[i]).append("',hobby)");
                } else {
                    sql.append(" or find_in_set('").append(hobbys).append("',hobby)");
                }
            }
            sql.append(")");
        }
        sql.append(" order by id desc ");
        sql.append(" limit ").append((userModel.getPage() - 1) * userModel.getLimit()).append(",").append(userModel.getLimit());
        return setList(sql.toString());
    }

    @Override
    public UserModel findById(int id) {
        String sql = "select * from user where id=" + id;
        List<UserModel> list = setList(sql);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public int changeEnable(int id) {
        String sql = "UPDATE  user  SET enable = ABS(enable -1) WHERE id = " + id;
        return JDBC.update(sql);
    }

    private List<UserModel> setList(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setAddress(resultSet.getString("address"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setEmail(resultSet.getString("email"));
                model.setEnable(resultSet.getInt("enable"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setUsername(resultSet.getString("username"));
                model.setHobby(resultSet.getString("hobby"));
                model.setBirthday(resultSet.getString("birthday"));

                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
//            JDBC.close();
        }
        return list;
    }
}
